#include "student.h"
#include "ui_student.h"
#include <QDebug>
#include <QMessageBox>
#include <QFileDialog>
#include <QSqlError>

Student::Student(QWidget *parent) :
    QWidget(parent),
    ui(new Ui::Student)
{
    ui->setupUi(this);

    db = QSqlDatabase::addDatabase("QMYSQL"); // 添加mysql
    db.setHostName("192.168.146.128");
    db.setUserName("root");
    db.setPassword("123456");
    db.setDatabaseName("test268");
    db.setPort(3306);

    if(!db.open())
    {
        qDebug()<<db.lastError()<<endl;
        return;
    }
    qDebug()<<"连接成功"<<endl;
    Insertdata();

    connect(ui->pushButton,&QPushButton::clicked,this,&Student::UpdateData);
}

void Student::Insertdata()
{
    QSqlQuery query; // 语句执行对象

//     查询stuID = i的学生信息
    int ID = 2;
    query.prepare("select * from StuInfo where id = :ID;");
    query.bindValue(":ID", ID);
    if (!query.exec())
    {
        qDebug() << db.lastError() << "查询学号失败" << endl;
        return;
    }

    if (query.next())
    {
        int StuID = query.value(0).toInt(); // 获取StuID

        // 查找成绩表中的课程编号与分数
        query.prepare("select lesID, sorce from SorceReport where stuID = :StuID");
        query.bindValue(":StuID", StuID); // 绑定StuID参数
        if (!query.exec())
        {
            qDebug() << db.lastError() << "查询课程编号与课程分数失败"<< endl;
            return;
        }

        // 设置表
        QStringList headers;
        headers << "课程" << "课程分数";
        ui->tableWidget->setColumnCount(2); // 设置列数
        ui->tableWidget->setHorizontalHeaderLabels(headers); // 水平方向

        ui->tableWidget->setRowCount(0);        // 从第一行开始插入
        int currentRow = 0;

        // 遍历SorceReport表中的数据，填充课程和成绩
        while (query.next())
        {
            int LesID = query.value("lesID").toInt(); // 获取LesID
            double Score = query.value("sorce").toDouble(); // 获取成绩

            // 使用LesID查询Lesson表，获取LesName
            QSqlQuery lessonQuery; // 新建查询对象
            lessonQuery.prepare("select lesName from Lesson where lesID = :LesID");
            lessonQuery.bindValue(":LesID", LesID); // 绑定LesID参数
            if (!lessonQuery.exec())
            {
                qDebug() << db.lastError() <<"查询课程名失败"<< endl;
                return;
            }

            if (lessonQuery.next())
            {
                QString LesName = lessonQuery.value("lesName").toString(); // 获取LesName

                // 填充课程名和分数
                QTableWidgetItem *CourseItem = new QTableWidgetItem(LesName);
                QTableWidgetItem *ScoreItem = new QTableWidgetItem(QString::number(Score));

                ui->tableWidget->insertRow(currentRow);
                ui->tableWidget->setItem(currentRow,0,CourseItem);
                ui->tableWidget->setItem(currentRow,1,ScoreItem);
                currentRow++;
            }
        }
        // 调整列宽自适应内容
        ui->tableWidget->horizontalHeader()->setSectionResizeMode(QHeaderView::Stretch);
    }
}

void Student::UpdateData()
{
    ui->tableWidget->setRowCount(0);  // 清除现有的行
    Insertdata();
}

Student::~Student()
{
    delete ui;
}


